package fashionshop.dal;

import java.sql.ResultSet;

import database.MySQLConnect;
import fashionshop.bll.ImportBLL;
import fashionshop.bll.OrderBLL;

public class ImportDAL {

	public ImportDAL(){
	}
	
	public static ResultSet LoadAll() throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sqlQuery = "SELECT import.* FROM import";
		return conn.executeQuery(sqlQuery);
	}
	
	public static ResultSet LoadByID(int id) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "SELECT import.*, staff.Name as StaffName FROM import, staff WHERE import.StaffID = staff.ID and import.ID = '%d'";
		String sqlQuery = String.format(sql, id);
		return conn.executeQuery(sqlQuery);
	}
	
	public static ResultSet LoadByCriteria(String staffName, String createDateFrom, String createDateTo) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "SELECT import.*, staff.Name as StaffName FROM import, staff WHERE import.StaffID = staff.ID and (staff.Name LIKE '%" + staffName + "%'";
		if(!createDateFrom.isEmpty())
			sql = sql +" and CreateDate >= '" + createDateFrom + "'";
		if(!createDateTo.isEmpty())
			sql = sql +" and CreateDate <= '" + createDateTo + "'";
		sql = sql + ")";
		System.out.println(sql);
		return conn.executeQuery(sql);
	}

	public static int Insert(ImportBLL importBLL) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "INSERT INTO import (`Sum`, `CreateDate`, `Note`) VALUES ('%.0f', '%s', '%s');";
		String sqlQuery = String.format(sql, importBLL.getSum(), importBLL.getCreateDate(), importBLL.getNote());
		return conn.executeUpdate(sqlQuery);
	}
	
	public static int Update(ImportBLL importBLL) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "UPDATE import SET `Sum`='%.0f', `Note`='%s' WHERE `ID`='%d'";
		String sqlQuery = String.format(sql, importBLL.getSum(), importBLL.getNote(), importBLL.getId());
		return conn.executeUpdate(sqlQuery);
	}
}
